﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.IO;

namespace Intorise.Common
{
    public class OfficeTools
    {
        /// <summary>
        /// 将excel文件导入为DataTable，采用OLEDB方式
        /// </summary>
        /// <param name="excelUri">电子表格文件URI</param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns></returns>
        public static DataTable GetDataTableFromExcel(string excelUri, string sheetName,int headerIndex=0)
        {
            DataTable table = new DataTable();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelUri + ";Extended Properties='Excel 8.0; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt.Rows.Count > 0)
            {
                table.TableName = dt.Rows[0]["TABLE_NAME"].ToString();
            }

            if (string.IsNullOrEmpty(table.TableName))
            {
                table.TableName = sheetName;
            }
            table.TableName = "[" + table.TableName + "]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM " + table.TableName, strConn);

            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet);
            }
            catch
            {
                throw;
            }
            myDataSet.Tables[0].TableName = table.TableName;
            return myDataSet.Tables[0];
        }
        /*
        /// <summary>
        /// 采用ExcelApp方式
        /// </summary>
        /// <param name="tempFileName"></param>
        /// <returns></returns>
        public static DataTable ImportExcelToDataTable(string tempFileName)
        {
            DataTable dt = new DataTable();

            //创建Excel对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            DateTime afterTime = DateTime.Now;
            excel.EnableEvents = false;
            excel.DisplayAlerts = false;
            excel.Visible = false;

            Microsoft.Office.Interop.Excel.Workbook wBook = excel.Workbooks.Open(tempFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            int rowCount = ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet).UsedRange.Rows.Count;
            int colCount = ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet).UsedRange.Columns.Count;

            try
            {
                string cellContent;

                for (int iRow = 1; iRow <= rowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    for (int iCol = 1; iCol <= colCount; iCol++)
                    {
                        cellContent = (((Microsoft.Office.Interop.Excel.Range)excel.Cells[iRow, iCol]).Value != null) ? ((Microsoft.Office.Interop.Excel.Range)excel.Cells[iRow, iCol]).Value.ToString() : "";
                        if (iRow == 1)
                        {
                            dt.Columns.Add(cellContent);
                        }
                        else
                        {
                            dr[iCol - 1] = cellContent;
                        }
                    }
                    if (iRow != 1)
                        dt.Rows.Add(dr);
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                wBook.Close(null, null, null);
                excel.Workbooks.Close();
                excel.Application.Quit();
                excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                wBook = null;
                excel = null;
            }
            return dt;
        }
        */
        //dt为数据源（数据表）
        //ExcelFileName 为要导出的Ｅｘｃｌｅ文件
        //ModelFile为模板文件，该文件与数据源中的表一致。否则数据会导出失败。
        //ModelFile文件里，需要有一张 与 dt.TableName 一致的表，而且字段也要一致。
        //注明：如果不用ModelFile的话，可以用一个空白Excel文件，不过，要去掉下面创建表的注释，让OleDb自己创建一个空白表。
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="ExcelFileName">为要导出的excel文件资源名称</param>
        /// <param name="ModelFile">模板文件，该文件与数据源中的表一致。否则数据会导出失败</param>
        public static void TableToExcelFile(DataTable dt, string ExcelFileName, string ModelFile, int skipCount = 0)
        {
            File.Delete(ExcelFileName);
            File.Copy(ModelFile, ExcelFileName);  //复制一个空文件，提供写入数据用

            if (File.Exists(ExcelFileName) == false)
            {
                throw new Exception("系统创建临时文件失败，请与系统管理员联系！");
            }
            if (dt == null)
            {
                throw new Exception("DataTable不能为空");
            }
            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;

            string connString;
            if (rows == 0)
            {
                throw new Exception("没有数据");
            }

            connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";

            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;
            //del
            objConn.Open();

            StringBuilder sb = new StringBuilder();


            sb.Append("INSERT INTO ");
            sb.Append(dt.TableName + " values( ");
            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append("@p" +i+ ",");
                else
                    sb.Append("@" + i + ")");
            }
            //建立插入动作的Command
            objCmd.CommandText = sb.ToString();
            OleDbParameterCollection param = objCmd.Parameters;
            for (int i = 0; i < cols; i++)
            {
                param.Add(new OleDbParameter("@" + i, OleDbType.LongVarWChar));
            }

            for (int x = skipCount; x < dt.Rows.Count; x++)
            {
                for (int i = 0; i < param.Count; i++)
                {
                    if (dt.Rows[x][i].ToString().Length > 255)
                    {
                        dt.Rows[x][i] = dt.Rows[x][i].ToString().Substring(0, 255);
                    }
                    param[i].Value = dt.Rows[x][i];
                }
                
                try
                {
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    for (int i = 0; i < param.Count; i++)
                    {
                        param[i].Value = "";
                    }
                    objCmd.ExecuteNonQuery();//插入一个空行
                }
            }
            // return "数据已成功导入Excel";
            objConn.Close();
        }
    }
}
